---
title: "SQL-PSVC-02: Provider service contracts"
summary: Retrieve service contracts for a provider location within an optional date window
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-PSVC-02 Provider service contracts

Retrieves the service-contract records for a provider location, ordered by effective date
descending, optionally filtered to contracts still active or expired within a caller-specified
day offset from the current date.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-PSVC-02 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (StringBuilder-assembled) via `SqlHelper.ResultSetQuery` |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | N/A (Application layer) |
| **Criteria property** | N/A |
| **Lifecycle method** | N/A |
| **Application class** | `clsProviderServiceContractList` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:ProviderLocationID` | Input | Decimal | N/A | Provider location surrogate key |
| `:Offset` | Input | Decimal | N/A | Day offset (default `721`); only bound when the expiry-date filter is appended |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER_SERVICE_CONTRACT](../database-tables.md#provider_service_contract) | SELECT | `PROV_SERVICE_CONTRACT_SEQ_NUM`, `PROVIDER_LOCATION_SEQ_NUM`, `MCO_PLAN_ID`, `EFF_DATE`, `EXP_DATE`, `PURCHASE_CODE`, `DISCOUNT_RATE`, `ADDITIONAL_FEE_DISCOUNT_RATE`, `MAX_CHARGE_AMT`, `CBP_SERVICE_CODE`, `SERVICE_CODE`, `SERVICE_TYPE_CODE`, `CONTACT_NAME`, `SERVICE_MODIFIER_CODE` |

## Sample SQL

```sql
-- Provider service contracts
SELECT
  PROV_SERVICE_CONTRACT_SEQ_NUM AS ContractID,
  PROVIDER_LOCATION_SEQ_NUM     AS ProviderLocationID,
  MCO_PLAN_ID                   AS McoPlanID,
  EFF_DATE                      AS EffectiveDate,
  EXP_DATE                      AS ExpirationDate,
  PURCHASE_CODE                 AS PurchaseCode,
  DISCOUNT_RATE                 AS DiscountRate,
  ADDITIONAL_FEE_DISCOUNT_RATE  AS AdditionalFeeDiscountRate,
  MAX_CHARGE_AMT                AS MaxChargeAmount,
  CBP_SERVICE_CODE              AS CBPServiceCode,
  SERVICE_CODE                  AS ServiceCode,
  SERVICE_TYPE_CODE             AS ServiceTypeCode,
  CONTACT_NAME                  AS ContactName,
  SERVICE_MODIFIER_CODE         AS ServiceModifierCode
FROM PROVIDER_SERVICE_CONTRACT
WHERE PROVIDER_LOCATION_SEQ_NUM = :ProviderLocationID
  AND (exp_date IS NULL OR exp_date > (sysdate - :Offset))
ORDER BY eff_date DESC
```

> The `exp_date` predicate is appended only when the caller requests the date-window filter;
> `inOffset` defaults to `721` days.

## Operations and processes
- [Load provider services and contracts](../operations/load-provider-services-and-contracts.md)

## Business rules
- [BR-PSVC-001: Service contract offset window](../business-rules.md#br-psvc-001-service-contracts-are-limited-to-the-offset-window)
- [BR-MCO-001: MCO plan match validation](../business-rules.md#br-mco-001-mco-plan-must-match-the-assigned-plan-id)
- [BR-DATE-001: Date-window effectiveness](../business-rules.md#br-date-001-dated-records-are-in-effect-within-their-date-window)

## Sources
- SAIF.Business.View.Provider/clsProviderServiceContract.vb